package it.univaq.f4i.iw.ex.album.model.impl;

import it.univaq.f4i.iw.ex.album.model.Album;
import it.univaq.f4i.iw.ex.album.model.Comment;
import it.univaq.f4i.iw.ex.album.model.Photo;
import it.univaq.f4i.iw.ex.album.model.PhotoAlbumDataLayer;
import it.univaq.f4i.iw.ex.album.model.User;
import it.univaq.f4i.iw.ex.album.utils.ImageHelpers;
import java.awt.Image;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.imageio.ImageIO;

/**
 *
 * @author IngegneriaDelWeb
 */
public class PhotoAlbumDataLayerMysqlImpl implements PhotoAlbumDataLayer {

    private PreparedStatement gAlbums_user, gAlbum, gFotos_album, gFotos_titolo, gFoto, aFoto, uFoto, dFoto, gComments, gUser;
    private String basepath_image, basepath_thumbnail;

    public PhotoAlbumDataLayerMysqlImpl(String basepath_image, String basepath_thumbnail, Connection connection) throws SQLException {

        this.basepath_image = basepath_image;
        this.basepath_thumbnail = basepath_thumbnail;

        //precompiliamo tutte le query utilizzate nella classe
        //precompile all the queries uses in this class
        gAlbums_user = connection.prepareStatement("SELECT * FROM album WHERE IDutente=?");
        gAlbum = connection.prepareStatement("SELECT * FROM album WHERE ID=?");
        gFotos_album = connection.prepareStatement("SELECT * FROM foto WHERE IDalbum=?");
        gFotos_titolo = connection.prepareStatement("SELECT * FROM foto WHERE titolo LIKE ?");
        gFoto = connection.prepareStatement("SELECT * FROM foto WHERE ID=?");
        //notare l'ultimo paametro extra di questa chiamata a
        //prepareStatement: lo usiamo per assicurarci che il JDBC
        //restituisca la chiave generata automaticamente per il
        //record inserito

        //note the last parameter in this call to prepareStatement:
        //it is used to ensure that the JDBC will sotre and return
        //the auto generated key for the inserted recors
        aFoto = connection.prepareStatement("INSERT INTO foto (titolo,tipo,IDalbum, image_uri, thumbnail_uri) VALUES(?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
        uFoto = connection.prepareStatement("UPDATE foto SET titolo=?,tipo=?,IDalbum=?,image_uri=?, thumbnail_uri=? WHERE ID=?");
        dFoto = connection.prepareStatement("DELETE FROM foto WHERE ID=?");
        gComments = connection.prepareStatement("SELECT * FROM commento WHERE IDfoto=?");
        gUser = connection.prepareStatement("SELECT * FROM utente WHERE ID=?");
    }

    //metodi "factory" che permettono di creare
    //e inizializzare opportune implementazioni
    //delle interfacce del modello dati, nascondendo
    //all'utente tutti i particolari
    //factory methods to create and initialize
    //suitable implementations of the data model interfaces,
    //hiding all the implementation details
    public Photo createPhoto() {
        return new PhotoMysqlImpl(this);
    }

    public Album createAlbum() {
        return new AlbumMysqlImpl(this);
    }

    public Comment createComment() {
        return new CommentMysqlImpl(this);
    }

    public User createUser() {
        return new UserMysqlImpl(this);
    }

    public Album getAlbum(int key) {
        Album result = null;
        ResultSet rs = null;
        try {
            gAlbum.setInt(1, key);
            rs = gAlbum.executeQuery();
            if (rs.next()) {
                //notare come utilizziamo il costrutture
                //"helper" della classe AlbumMysqlImpl
                //per creare rapidamente un'istanza a
                //partire dal record corrente
                //note how we use here the helper constructor
                //of the AlbumMysqlImpl class to quickly
                //create an instance from the current record
                result = new AlbumMysqlImpl(this, rs);
            }
        } catch (SQLException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
            } catch (SQLException ex) {
                //
            }
        }
        return result;
    }

    public List<Album> getAlbumsByUser(User proprietario) {
        List<Album> result = new ArrayList();
        ResultSet rs = null;
        try {
            gAlbums_user.setInt(1, proprietario.getKey());
            rs = gAlbums_user.executeQuery();
            while (rs.next()) {
                result.add(new AlbumMysqlImpl(this, rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
            } catch (SQLException ex) {
                //
            }
        }
        return result;
    }

    public Photo addPhoto(Photo foto) {
        PhotoMysqlImpl iFoto = (PhotoMysqlImpl) foto;
        ResultSet keys = null;
        try {
            //questa prima parte del metodo implementa
            //la nostra politica di upload delle immagini
            //associate alle foto.
            //Se la foto non ha ancora una URI che punta
            //a un'immagine su disco, ma ha uno stream da cui
            //leggere l'immagine, allora provvediamo a scrivere
            //lo stream e la corrispondente miniatura nel nostro
            //file system e inseriamo nell'oggetto le uri così
            //ottenute.

            //the first part of this method implements our uploading policy
            //for the image associated to the photo.
            //If the photo does not have a URI that points to an image on disk, 
            //but it has a stream from which the image can be read, then we
            //write the stream and the corresponding thumbnail in our file system 
            //and insert in the object the corresponding so uri.
            if (iFoto.getImageUri().isEmpty() && iFoto.getImage() != null) {
                iFoto.setImageUri(uploadFotoFile(iFoto.getImageType(), iFoto.getImage()));
                iFoto.setThumbnail_uri(generateThumbnail(iFoto.getImageUri()));
            }
            //da questo punto in poi procediamo con un normale inserimento
            //from this point on, we proceed as in a normal insertion
            aFoto.setString(1, iFoto.getTitle());
            aFoto.setString(2, iFoto.getImageType());
            aFoto.setInt(3, iFoto.getAlbum().getKey());
            aFoto.setString(4, iFoto.getImageUri());
            aFoto.setString(5, iFoto.getThumbnailUri());
            //executeUpdate ritorna il numero di record modificati
            //(inseriti, in questo caso)
            //executeUpdate returns the number of records modified
            //(added, in this case)            
            if (aFoto.executeUpdate() == 1) {
                //per leggere la chiave generata dal database
                //per il record appena inserito, usiamo il metodo
                //getGeneratedKeys sullo statement.
                //to read the generated record key from the database
                //we use the getGeneratedKeys method on the same statement
                keys = aFoto.getGeneratedKeys();
                //il valore restituito è un ResultSet con un record
                //per ciascuna chiave generata (uno solo nel nostro caso)
                //the returned value is a ResultSet with a distinct record for
                //each generated key (only one in our case)
                if (keys.next()) {
                    //i campi del record sono le componenti della chiave
                    //(nel nostro caso, un solo intero)
                    //the record fields are the key componenets
                    //(a single integer in our case)
                    return getPhoto(keys.getInt(1));
                    //restituiamo la foto appena inserita RICARICATA
                    //dal database tramite le API del modello. In tal
                    //modo terremo conto di ogni modifica apportata
                    //durante la fase di inserimento
                    //we return the just-inserted photo RELOADED from the
                    //database through our API. In this way, the resulting
                    //object will ambed any data correction performed by
                    //the DBMS
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                keys.close();
            } catch (SQLException ex) {
                //
            }
        }
        return null;
    }

    public Photo updatePhoto(Photo foto) {
        PhotoMysqlImpl iFoto = (PhotoMysqlImpl) foto;
        try {
            //questa prima parte del metodo implementa
            //la nostra politica di upload delle immagini
            //associate alle foto.
            //Se la foto ha asociato uno stream,
            //allora lo usiamo per sostituire l'immagine
            //eventualmente già associata alla foto
            //(tramite la sua uri)

            //the first part of this method implements our uploading policy
            //for the image associated to the photo.
            //If the photo has a stream from which the image can be read, then we
            //use such stream to update the image and the corresponding thumbnail 
            //pointed by the image uri
            if (iFoto.getImage() != null) {
                if (!iFoto.getImageUri().isEmpty()) {
                    deleteFotoFile(iFoto);
                }
                iFoto.setImageUri(uploadFotoFile(iFoto.getImageType(), iFoto.getImage()));
                iFoto.setThumbnail_uri(generateThumbnail(iFoto.getImageUri()));
            }
            //da questo punto in poi procediamo con un normale aggiornamento
            uFoto.setString(1, iFoto.getTitle());
            uFoto.setString(2, iFoto.getImageType());
            uFoto.setInt(3, iFoto.getAlbum().getKey());
            uFoto.setString(4, iFoto.getImageUri());
            uFoto.setString(5, iFoto.getThumbnailUri());
            uFoto.setInt(6, iFoto.getKey());
            if (uFoto.executeUpdate() == 1) {
                return getPhoto(iFoto.getKey());
                //restituiamo la foto appena modificata RICARICATA
                //dal database tramite le API del modello. In tal
                //modo terremo conto di ogni modifica apportata
                //durante la fase di aggiornamento

                //we return the just-updated photo RELOADED from the
                //database through our API. In this way, the resulting
                //object will ambed any data correction performed by
                //the DBMS
            }
        } catch (SQLException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Photo deletePhoto(Photo foto) {
        try {
            dFoto.setInt(1, foto.getKey());
            //se la cancellazione nel DB va a buon fine,
            //cancelliamo anche i file associati dal file system
            //if the database deletion succeeds, we delete also
            //the associated files from the filesystem
            if (dFoto.executeUpdate() == 1) {
                deleteFotoFile(foto);
                return foto;
                //restituiamo la foto appena cancellata
                //in modo da poterla usare ancora nel codice
                //return the just-deleted photo to make it
                //available in the code (maybe we may re-insert it)
            }
        } catch (SQLException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public List<Photo> getPhotoByTitle(String hint) {
        List<Photo> result = new ArrayList();
        ResultSet rs = null;
        try {
            gFotos_titolo.setString(1, "%" + hint + "%");
            rs = gFotos_titolo.executeQuery();
            while (rs.next()) {
                result.add(new PhotoMysqlImpl(this, rs));
            }

        } catch (SQLException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
            } catch (SQLException ex) {
                //
            }
        }
        return result;
    }

    public Photo getPhoto(int key) {
        Photo result = null;
        ResultSet rs = null;
        try {
            gFoto.setInt(1, key);
            rs = gFoto.executeQuery();
            if (rs.next()) {
                result = new PhotoMysqlImpl(this, rs);
            }
        } catch (SQLException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
            } catch (SQLException ex) {
                //
            }
        }
        return result;
    }

    public List<Comment> getCommentsByPhoto(Photo foto) {
        List<Comment> result = new ArrayList();
        ResultSet rs = null;
        try {
            gComments.setInt(1, foto.getKey());
            rs = gComments.executeQuery();
            while (rs.next()) {
                result.add(new CommentMysqlImpl(this, rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
            } catch (SQLException ex) {
                //
            }
        }
        return result;



    }

    public List<Photo> getPhotoByAlbum(Album album) {
        List<Photo> result = new ArrayList();
        ResultSet rs = null;
        try {
            gFotos_album.setInt(1, album.getKey());
            rs = gFotos_album.executeQuery();
            while (rs.next()) {
                result.add(new PhotoMysqlImpl(this, rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
            } catch (SQLException ex) {
                //
            }
        }
        return result;
    }

    public User getUser(int key) {
        User result = null;
        ResultSet rs = null;
        try {
            gUser.setInt(1, key);
            rs = gUser.executeQuery();
            if (rs.next()) {
                result = new UserMysqlImpl(this, rs);
            }
        } catch (SQLException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
            } catch (SQLException ex) {
                //
            }
        }
        return result;
    }

    ///////////////////////////////////////////////////////
    public Album addAlbum(Album album) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public Album updateAlbum(Album album) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public Album deleteAlbum(Album album) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public Comment getComment(int key) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public Comment addComment(Comment commento) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public Comment updateComment(Comment commento) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public Comment deleteComment(Comment commento) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public User addUser(User utente) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public User updateUser(User utente) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public User deleteUser(User utente) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    //////////////// metodi helper privati ////////////////
    //dato uno stream immagine e il suo tipo, lo inserisce
    //opportunamente nel file system
    //////////////// private helper methods ////////////////
    //dato uno stream immagine e il suo tipo, lo inserisce
    //opportunamente nel file system
    //given an image stream and its type, copy it on the file system
    private String uploadFotoFile(String tipo, InputStream is) {
        OutputStream os = null;
        try {
            //notare come costuriamo un nome univoco per la nuova immagine
            String filename = UUID.randomUUID().toString() + "." + tipo.substring(1 + tipo.indexOf("/"));
            String img_uri = basepath_image + "/" + filename;
            os = new FileOutputStream(img_uri);
            byte[] buffer = new byte[1024];
            int len;
            while ((len = is.read(buffer)) >= 0) {
                os.write(buffer, 0, len);
            }
            return img_uri;
        } catch (FileNotFoundException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                os.close();
                is.close();
            } catch (IOException ex) {
                //
            }
        }
        return null;
    }

    //cancella da file system i files contenenti
    //l'immagine e la miniatura associati a una foto
    //deletes from the file system the photo image file and
    //the correspknding thumbnail
    private void deleteFotoFile(Photo foto) {
        if (foto != null) {
            File f = new File(foto.getImageUri());
            if (f.exists()) {
                f.delete();
            }
            f = new File(foto.getThumbnailUri());
            if (f.exists()) {
                f.delete();
            }
        }
    }

    //genera e scrive opportunamentesu disco la miniatura
    //associata all'imagine puntata dalla img_uri
    //generates and writes on disk the thumbnail for the
    //image pointed by umg_uri
    private String generateThumbnail(String img_uri) {
        String filename = new File(img_uri).getName();
        String name = filename.substring(0, filename.lastIndexOf("."));
        String thumb_uri = basepath_thumbnail + "/" + name + ".jpg";
        try {
            Image img = ImageIO.read(new File(img_uri));
            ImageHelpers.saveThumbnail(img, 160, ImageHelpers.HORIZONTAL, new File(thumb_uri));
            return thumb_uri;
        } catch (IOException ex) {
            Logger.getLogger(PhotoAlbumDataLayer.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
}
